library(dplyr)
library(magrittr)

Children_Activities <- ENUT_C5
Demographic_Information <- ENUT_C3

initial_dataset <- merge(Children_Activities, 
                         Demographic_Information[, c("DIRECTORIO", "SECUENCIA_P", "ORDEN", "P6040", "P6020", "P1173", "P425", "P5762", "P5762S1", "P5754", "P5754S1")], 
                         by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN"), 
                         all.x = TRUE)

names(initial_dataset)[names(initial_dataset) == "P6040"] <- "age"

# Recoding P6020: if 2 then 0, if 1 then remains 1
initial_dataset$P6020[initial_dataset$P6020 == 2] <- 0

# Renaming P6020 to male
names(initial_dataset)[names(initial_dataset) == "P6020"] <- "male"

initial_dataset$F_EXP <- NULL

names(initial_dataset)[names(initial_dataset) == "P1173"] <- "ethnicity"

names(initial_dataset)[names(initial_dataset) == "P425"] <- "relationship_head"


# Subset dataset to keep only rows where relationship_head equals 3
second_dataset <- initial_dataset[initial_dataset$relationship_head == 3, ]

names(second_dataset)[names(second_dataset) == "P5762S1"] <- "ORDEN_mom"

names(second_dataset)[names(second_dataset) == "P5754S1"] <- "ORDEN_dad"



# Assuming ENUT_C7_1 has DIRECTORIO, SECUENCIA_P, and ORDEN (for matching ORDEN_mom in second_dataset)
second_dataset <- second_dataset %>%
  left_join(ENUT_C7_1 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1151),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(work_hours_mom = P1151) %>%

  # Assuming ENUT_C7_1 has DIRECTORIO, SECUENCIA_P, and ORDEN (for matching ORDEN_mom in second_dataset)
  second_dataset <- second_dataset %>%
  left_join(ENUT_C7_1 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1151),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(work_hours_mom = P1151) %>% 
  
  second_dataset <- second_dataset %>%
  left_join(ENUT_C7_1 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P6500),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(salary_dad = if_else(is.na(P6500), 0, P6500)) %>% 

second_dataset <- second_dataset %>%
  left_join(ENUT_C7_1 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P6500),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(salary_mom = if_else(is.na(P6500), 0, P6500)) %>% 


second_dataset <- second_dataset %>%
  left_join(ENUT_C3 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P6040),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(age_dad = if_else(is.na(P6040), 0, P6040)) %>% 

second_dataset <- second_dataset %>%
  left_join(ENUT_C3 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P6040),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(age_mom = if_else(is.na(P6040), 0, P6040)) %>% 


second_dataset <- second_dataset %>%
  left_join(ENUT_C6 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P6210),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(education_mom = if_else(is.na(P6210), 0, P6210)) %>%
  

second_dataset <- second_dataset %>%
  left_join(ENUT_C6 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P6210),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(education_dad = if_else(is.na(P6210), 0, P6210)) %>%
  


# Add ethnicity for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C3 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1173),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(ethnicity_mom = if_else(is.na(P1173), "Unknown", as.character(P1173))) %>%
  

# Add ethnicity for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C3 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1173),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(ethnicity_dad = if_else(is.na(P1173), "Unknown", as.character(P1173))) %>%
  


# Add jugar (P1137S1) for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S1),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(jugar_mom = if_else(is.na(P1137S1), "NA", as.character(P1137S1))) %>%

# Add jugar (P1137S1) for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S1),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(jugar_dad = if_else(is.na(P1137S1), "NA", as.character(P1137S1))) %>%
  select(-P1137S1)  %>% 


# Add hours and minutes playing (P1137S1A1 and P1137S1A2) for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S1A1, P1137S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(playing_hours_mom = if_else(is.na(P1137S1A1), 0, P1137S1A1),
         playing_minutes_mom = if_else(is.na(P1137S1A2), 0, P1137S1A2)) %>%
  select(-P1137S1A1, -P1137S1A2) %>% 

# Add hours and minutes playing (P1137S1A1 and P1137S1A2) for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S1A1, P1137S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(playing_hours_dad = if_else(is.na(P1137S1A1), 0, P1137S1A1),
         playing_minutes_dad = if_else(is.na(P1137S1A2), 0, P1137S1A2)) %>%
  select(-P1137S1A1, -P1137S1A2) %>% 


# Add reading activity (P1137S2) for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(reading_mom = if_else(is.na(P1137S2), "NA", as.character(P1137S2))) %>%
  select(-P1137S2)  %>% 

# Add reading activity (P1137S2) for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(reading_dad = if_else(is.na(P1137S2), "NA", as.character(P1137S2))) %>%
  select(-P1137S2) %>% 


# Add hours and minutes reading (P1137S2A1 and P1137S2A2) for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S2A1, P1137S2A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(reading_hours_mom = if_else(is.na(P1137S2A1), 0, P1137S2A1),
         reading_minutes_mom = if_else(is.na(P1137S2A2), 0, P1137S2A2)) %>%
  select(-P1137S2A1, -P1137S2A2) %>% 

# Add hours and minutes reading (P1137S2A1 and P1137S2A2) for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S2A1, P1137S2A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(reading_hours_dad = if_else(is.na(P1137S2A1), 0, P1137S2A1),
         reading_minutes_dad = if_else(is.na(P1137S2A2), 0, P1137S2A2)) %>%
  select(-P1137S2A1, -P1137S2A2) %>% 


# Add park activity (P1137S3) for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S3),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(park_mom = if_else(is.na(P1137S3), "NA", as.character(P1137S3))) %>%
  select(-P1137S3)  %>% 

# Add park activity (P1137S3) for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S3),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(park_dad = if_else(is.na(P1137S3), "NA", as.character(P1137S3))) %>%
  select(-P1137S3)  %>% 


# Add hours and minutes at the park (P1137S3A1 and P1137S3A2) for mom
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S3A1, P1137S3A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(park_hours_mom = if_else(is.na(P1137S3A1), 0, P1137S3A1),
         park_minutes_mom = if_else(is.na(P1137S3A2), 0, P1137S3A2)) %>%
  select(-P1137S3A1, -P1137S3A2)  %>% 

# Add hours and minutes at the park (P1137S3A1 and P1137S3A2) for dad
second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1137S3A1, P1137S3A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(park_hours_dad = if_else(is.na(P1137S3A1), 0, P1137S3A1),
         park_minutes_dad = if_else(is.na(P1137S3A2), 0, P1137S3A2)) %>%
  select(-P1137S3A1, -P1137S3A2)  %>% 

## From C2 append##
## total people in home P6008
## for owners pay P5091
## for renters pay P5092

## From C1 append##
## P4030S1A1 stratum##
## REGION region##


# Append stratum and region from ENUT_C1 to second_dataset
second_dataset <- second_dataset %>%
  left_join(ENUT_C1 %>%
              select(DIRECTORIO, P4030S1A1, REGION),
            by = "DIRECTORIO") %>%
  # Optionally rename P4030S1A1 to 'stratum' for clarity
  rename(stratum = P4030S1A1)

# First, ensure that ENUT_C2 is prepared with unique combinations of DIRECTORIO and SECUENCIA_P if necessary
# This step assumes ENUT_C2 might not have unique combinations and prepares it by selecting the first occurrence
# Note: This might not be necessary if your dataset already ensures uniqueness for these combinations.
ENUT_C2_unique <- ENUT_C2 %>%
  group_by(DIRECTORIO, SECUENCIA_P) %>%
  slice(1) %>%
  ungroup()

# Now perform the left_join with second_dataset, matching by both DIRECTORIO and SECUENCIA_P
second_dataset <- second_dataset %>%
  left_join(ENUT_C2_unique %>%
              select(DIRECTORIO, SECUENCIA_P, P6008, P5091, P5092),
            by = c("DIRECTORIO", "SECUENCIA_P")) %>%
  rename(total_people_home = P6008,
         owners_pay = P5091,
         renters_pay = P5092)

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1128S5A1, P1128S5A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(elderlycare_hours_mom = if_else(is.na(P1128S5A1), 0, P1128S5A1),
         elderlycare_minutes_mom = if_else(is.na(P1128S5A2), 0, P1128S5A2)) %>%
  select(-P1128S5A1, -P1128S5A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1128S5A1, P1128S5A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(elderlycare_hours_dad = if_else(is.na(P1128S5A1), 0, P1128S5A1),
         elderlycare_minutes_dad = if_else(is.na(P1128S5A2), 0, P1128S5A2)) %>%
  select(-P1128S5A1, -P1128S5A2)  


second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1128S7A1, P1128S7A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(disabledcare_hours_mom = if_else(is.na(P1128S7A1), 0, P1128S7A1),
         disabledcare_minutes_mom = if_else(is.na(P1128S7A2), 0, P1128S7A2)) %>%
  select(-P1128S7A1, -P1128S7A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1128S7A1, P1128S7A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(disabledcare_hours_dad = if_else(is.na(P1128S7A1), 0, P1128S7A1),
         disabledcare_minutes_dad = if_else(is.na(P1128S7A2), 0, P1128S7A2)) %>%
  select(-P1128S7A1, -P1128S7A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1136S1A1, P1136S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(clean_hours_mom = if_else(is.na(P1136S1A1), 0, P1136S1A1),
         clean_minutes_mom = if_else(is.na(P1136S1A2), 0, P1136S1A2)) %>%
  select(-P1136S1A1, -P1136S1A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1136S1A1, P1136S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(clean_hours_dad = if_else(is.na(P1136S1A1), 0, P1136S1A1),
         clean_minutes_dad = if_else(is.na(P1136S1A2), 0, P1136S1A2)) %>%
  select(-P1136S1A1, -P1136S1A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1142S1A1, P1142S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(wash_hours_mom = if_else(is.na(P1142S1A1), 0, P1142S1A1),
         wash_minutes_mom = if_else(is.na(P1142S1A2), 0, P1142S1A2)) %>%
  select(-P1142S1A1, -P1142S1A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1142S1A1, P1142S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(wash_hours_dad = if_else(is.na(P1142S1A1), 0, P1142S1A1),
         wash_minutes_dad = if_else(is.na(P1142S1A2), 0, P1142S1A2)) %>%
  select(-P1142S1A1, -P1142S1A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1143S1A1, P1143S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(cook_hours_mom = if_else(is.na(P1143S1A1), 0, P1143S1A1),
         cook_minutes_mom = if_else(is.na(P1143S1A2), 0, P1143S1A2)) %>%
  select(-P1143S1A1, -P1143S1A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1143S1A1, P1143S1A2),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(cook_hours_dad = if_else(is.na(P1143S1A1), 0, P1143S1A1),
         cook_minutes_dad = if_else(is.na(P1143S1A2), 0, P1143S1A2)) %>%
  select(-P1143S1A1, -P1143S1A2)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1109, P1108),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(feel_fair_mom = if_else(is.na(P1109), 0, P1109),
         feel_time_mom = if_else(is.na(P1108), 0, P1108)) %>%
  select(-P1109, -P1108)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1109, P1108),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(feel_fair_dad = if_else(is.na(P1109), 0, P1109),
         feel_time_dad = if_else(is.na(P1108), 0, P1108)) %>%
  select(-P1109, -P1108)  


second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1107S1, P1107S2, P1107S3, P1107S4, P1107S5, P1107S6),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(workMomGood_mom = if_else(is.na(P1107S1), 0, P1107S1),
         bothEarn_mom = if_else(is.na(P1107S2), 0, P1107S2),
         goalMarriageKids_mom = if_else(is.na(P1107S3), 0, P1107S3),
         womenHomeSkill_mom = if_else(is.na(P1107S4), 0, P1107S4),
         equalFunRights_mom = if_else(is.na(P1107S5), 0, P1107S5),
         manHeadHousen_mom = if_else(is.na(P1107S6), 0, P1107S6)) %>%
  select(-P1107S1, -P1107S2,-P1107S3, -P1107S4, -P1107S5, -P1107S6)  

second_dataset <- second_dataset %>%
  left_join(ENUT_C8 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P1107S1, P1107S2, P1107S3, P1107S4, P1107S5, P1107S6),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(workMomGood_dad = if_else(is.na(P1107S1), 0, P1107S1),
         bothEarn_dad = if_else(is.na(P1107S2), 0, P1107S2),
         goalMarriageKids_dad = if_else(is.na(P1107S3), 0, P1107S3),
         womenHomeSkill_dad = if_else(is.na(P1107S4), 0, P1107S4),
         equalFunRights_dad = if_else(is.na(P1107S5), 0, P1107S5),
         manHeadHousen_dad = if_else(is.na(P1107S6), 0, P1107S6)) %>%

  final_dataset_childcare <- final_dataset_childcare %>%
  left_join(ENUT_C3 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P426),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_dad" = "ORDEN")) %>%
  mutate(civil_dad = if_else(is.na(P426), 0, P426))

final_dataset_childcare <- final_dataset_childcare %>%
  select(-P426)

final_dataset_childcare <- final_dataset_childcare %>%
  left_join(ENUT_C3 %>%
              select(DIRECTORIO, SECUENCIA_P, ORDEN, P426),
            by = c("DIRECTORIO", "SECUENCIA_P", "ORDEN_mom" = "ORDEN")) %>%
  mutate(civil_mom = if_else(is.na(P426), 0, P426))
final_dataset_childcare <- final_dataset_childcare %>%
  select(-P426)

View(second_dataset)
# Ensure the writexl package is installed and loaded
# install.packages("writexl")
install.packages("writexl")
# Load the package
library(openxlsx)

# Write your dataframe to an Excel file with the specified name
write.xlsx(final_dataset_childcare, file = "final_final_daet_childcare.xlsx")



